﻿
using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
using DotNetNuke.Framework.Providers;

namespace gdlmo.DNN.Modules.Article.Data 
{
    
	public class SqlDataProvider : DataProvider 
	{
        
		private const string providerType = "data";
        
		#region Private Members
		private ProviderConfiguration _providerConfiguration = ProviderConfiguration.GetProviderConfiguration(providerType);
		private string _connectionString;
		private string _providerPath;
		private string _objectQualifier;
		private string _databaseOwner;
		#endregion
        
		#region Constructors
		public SqlDataProvider()
		{
			Provider objProvider = ((Provider)_providerConfiguration.Providers[_providerConfiguration.DefaultProvider]);
			if (objProvider.Attributes["connectionStringName"] != "" && System.Configuration.ConfigurationSettings.AppSettings[objProvider.Attributes["connectionStringName"]] != "") 
			{
				_connectionString = System.Configuration.ConfigurationSettings.AppSettings[objProvider.Attributes["connectionStringName"]];
			} 
			else 
			{
				_connectionString = objProvider.Attributes["connectionString"];
			}
			_providerPath = objProvider.Attributes["providerPath"];
			_objectQualifier = objProvider.Attributes["objectQualifier"];
			if (_objectQualifier != "" & _objectQualifier.EndsWith("_") == false) 
			{
				_objectQualifier += "_";
			}
			_databaseOwner = objProvider.Attributes["databaseOwner"];
			if (_databaseOwner != "" & _databaseOwner.EndsWith(".") == false) 
			{
				_databaseOwner += ".";
			}
		}
		#endregion

		#region Properties
		public string ConnectionString 
		{
			get 
			{
				return _connectionString;
			}
		}

		public string ProviderPath 
		{
			get 
			{
				return _providerPath;
			}
		}

		public string ObjectQualifier 
		{
			get 
			{
				return _objectQualifier;
			}
		}

		public string DatabaseOwner 
		{
			get 
			{
				return _databaseOwner;
			}
		}
		#endregion

		#region Private Methods
		private object GetNull(object Field)
		{
			return DotNetNuke.Common.Utilities.Null.GetNull(Field, DBNull.Value);
		}

		#endregion
		
		#region "Article Methods"
		public override IDataReader GetArticle(int ArticleID)
		{
			return (IDataReader)SqlHelper.ExecuteReader(ConnectionString, DatabaseOwner + ObjectQualifier + "gdlmo_ArticleGet", ArticleID);
		}
	
		public override IDataReader ListArticle()
		{
			return (IDataReader)SqlHelper.ExecuteReader(ConnectionString, DatabaseOwner + ObjectQualifier + "gdlmo_ArticleList");
		}
	
		public override IDataReader GetArticleByCategory(int CategoryId )
		{
			return (IDataReader)SqlHelper.ExecuteReader(ConnectionString, DatabaseOwner + ObjectQualifier + "gdlmo_ArticleGetByCategory", CategoryId);
		}
			
//		public override int AddArticle(int CategoryId , string Title , DateTime CreatedDate , string CreatedByUser , string CopyFrom , string Author , int Hits , bool IsPassed , string FileName , string IP , string DeptName , string Summary)
//		{
//			return int.Parse(SqlHelper.ExecuteScalar(ConnectionString, DatabaseOwner + ObjectQualifier + "gdlmo_ArticleAdd", CategoryId, Title, CreatedDate, CreatedByUser, CopyFrom, Author, Hits, IsPassed, FileName, IP, DeptName, Summary).ToString());
//		}		

		public override int AddArticle(int CategoryId , string Title , DateTime CreatedDate , string CreatedByUser , string CopyFrom , string Author , int Hits , bool IsPassed , string IP , string DeptName , string Summary,string ArticleNames)
		{
			return int.Parse(SqlHelper.ExecuteScalar(ConnectionString, DatabaseOwner + ObjectQualifier + "gdlmo_ArticleAdd", CategoryId, Title, CreatedDate, CreatedByUser, CopyFrom, Author, Hits, IsPassed, IP, DeptName, Summary,ArticleNames).ToString());
		}

		public override void UpdateArticle(int ArticleID, int CategoryId , string Title , DateTime CreatedDate , string CreatedByUser , string CopyFrom , string Author , int Hits , bool IsPassed , string IP , string DeptName , string Summary)
		{
			SqlHelper.ExecuteNonQuery(ConnectionString, DatabaseOwner + ObjectQualifier + "gdlmo_ArticleUpdate", ArticleID, CategoryId, Title, CreatedDate, CreatedByUser, CopyFrom, Author, Hits, IsPassed, IP, DeptName, Summary);
		}
	
		/// <summary>
		/// 更新文章及附件
		/// </summary>
		/// <param name="ArticleID">文章主关键字</param>
		/// <param name="CategoryId">文章所属分类</param>
		/// <param name="Title">文章标题</param>
		/// <param name="CreatedDate">创建日期</param>
		/// <param name="CreatedByUser">创建文章的用户</param>
		/// <param name="CopyFrom">文章来源</param>
		/// <param name="Author">作者</param>
		/// <param name="Hits">点击数</param>
		/// <param name="IsPassed">是否审核通过</param>
		/// <param name="IP">上传者的ip</param>
		/// <param name="DeptName">部门名称</param>
		/// <param name="Summary">摘要</param>
		/// <param name="ArticleNames">附件文件名字符串，用“;”分割</param>
		public override void UpdateArticle(int ArticleID, int CategoryId , string Title , DateTime CreatedDate , string CreatedByUser , string CopyFrom , string Author , int Hits , bool IsPassed , string IP , string DeptName , string Summary,string ArticleNames)
		{
			SqlHelper.ExecuteNonQuery(ConnectionString, DatabaseOwner + ObjectQualifier + "gdlmo_ArticleAndAccesoryUpdate", ArticleID, CategoryId, Title, CreatedDate, CreatedByUser, CopyFrom, Author, Hits, IsPassed, IP, DeptName, Summary,ArticleNames);
		}

		
		public override void DeleteArticle(int ArticleID)
		{
			SqlHelper.ExecuteNonQuery(ConnectionString, DatabaseOwner + ObjectQualifier + "gdlmo_ArticleDelete", ArticleID);
		}			

		
		/// <summary>
		/// 获取文章记录总数
		/// </summary>
		/// <returns></returns>
		public override int GetArticleCount()
		{
			return (int)(SqlHelper.ExecuteScalar(this.ConnectionString, this.DatabaseOwner + this.ObjectQualifier + "gdlmo_ArticleGetRcount"));			
		}
		
		/// <summary>
		/// 根据分类编号获取记录总数
		/// </summary>
		/// <param name="CategoryId">分类编号</param>
		/// <returns>记录总数</returns>
		public override int GetArticleCount(int CategoryId)
		{
			return (int)(SqlHelper.ExecuteScalar(this.ConnectionString, this.DatabaseOwner + this.ObjectQualifier + "gdlmo_ArticleGetRcountByCategory",CategoryId));			
		}
		/// <summary>
		/// 根据分类编号获取指定时间范围内的记录总数
		/// </summary>
		/// <param name="CategoryId">分类编号</param>
		/// <param name="StartDate">起始时间</param>
		/// <param name="EndDate">结束时间</param>
		/// <returns>记录总数</returns>
		public override int GetArticleCount(int CategoryId,DateTime StartDate,DateTime EndDate)
		{
			return (int)(SqlHelper.ExecuteScalar(this.ConnectionString, this.DatabaseOwner + this.ObjectQualifier + "gdlmo_ArticleGetRcountByDateCategory",CategoryId,StartDate,EndDate));			
		}

		/// <summary>
		/// 取得指定分类，指定页序，分页大小的分页数据
		/// </summary>
		/// <param name="PageIndex">页序</param>
		/// <param name="PageSize">页大小</param>
		/// <param name="PageCount">总页数</param>
		/// <returns>记录集</returns>
		public override IDataReader GetPageData(int PageIndex,int PageSize)
		{
			string strWhere =" AND " + STRWHERE;
//			int Rcount = (int)(SqlHelper.ExecuteScalar(this.ConnectionString, this.DatabaseOwner + this.ObjectQualifier + "gdlmo_GetCount",TABLENAME,KEYFIELD,strWhere));
//			if ((Rcount % PageSize) > 0)
//			{
//				PageCount = (Rcount / PageSize) + 1;
//			}
//			else
//			{
//				PageCount = Rcount / PageSize;
//			}	
			return (IDataReader)SqlHelper.ExecuteReader(ConnectionString, DatabaseOwner + ObjectQualifier + "gdlmo_Article_GetPagingData", TABLENAME,FIELDLIST,ORDERFIELD,KEYFIELD,PageIndex+1,PageSize,strWhere,ORDERTYPE);		
		}

		public override IDataReader GetPageData(int CategoryId,int PageIndex,int PageSize)
		{
			string strWhere = string.Empty;
            if( CategoryId != -1)
                strWhere = " AND CategoryId=" + CategoryId.ToString() + " "; 
			return (IDataReader)SqlHelper.ExecuteReader(ConnectionString, DatabaseOwner + ObjectQualifier + "gdlmo_Article_GetPagingData", TABLENAME,FIELDLIST,ORDERFIELD,KEYFIELD,PageIndex+1,PageSize,strWhere,ORDERTYPE);				
		}
//		public override IDataReader GetPageData(int PageIndex,int PageSize,string Title)
//		{
//			string strWhere = " Title=" + Title + " ";
//			return (IDataReader)SqlHelper.ExecuteReader(ConnectionString, DatabaseOwner + ObjectQualifier + "gdlmo_Article_GetPagingData", TABLENAME,FIELDLIST,ORDERFIELD,KEYFIELD,PageIndex,PageSize,strWhere,ORDERTYPE);	
//		}
//		public override IDataReader GetPageData(int CategoryId,int PageIndex,int PageSize,string Title)
//		{	
//			string strWhere = " CategoryId=" + CategoryId.ToString() + " AND Title=" + Title + " ";
//			return (IDataReader)SqlHelper.ExecuteReader(ConnectionString, DatabaseOwner + ObjectQualifier + "gdlmo_Article_GetPagingData", TABLENAME,FIELDLIST,ORDERFIELD,KEYFIELD,PageIndex,PageSize,strWhere,ORDERTYPE);	
//		}
		/// <summary>
		/// 取得分页数据
		/// </summary>
		/// <param name="CategoryId">分类编号</param>
		/// <param name="Title">标题</param>
		/// <param name="StartCreatedDate">开始时间段</param>
		/// <param name="EndCreatedDate">结束时间段</param>
		/// <param name="Author">作者</param>
		/// <param name="DeptName">部门名称</param>
		/// <param name="Summary">摘要</param>
		/// <param name="PageIndex">页序</param>
		/// <param name="PageSize">页大小</param>
		/// <param name="PageCount">返回值参的页总数</param>
		/// <returns>分页记录集</returns>
		public override IDataReader GetPageData(int CategoryId,string Title , string StartCreatedDate, string EndCreatedDate , string Author , string DeptName , string Summary,int PageIndex,int PageSize)
		{
			System.Text.StringBuilder strWhere = new System.Text.StringBuilder(300);
			if(CategoryId>0)	strWhere.Append(" AND CategoryId = " + CategoryId.ToString());
			if(Title !=null && Title.Length>0)		strWhere.Append(" AND Title LIKE '%" + Title + "%' ");			
            if (StartCreatedDate != null && StartCreatedDate != "" && EndCreatedDate != "") strWhere.Append(" AND CreatedDate BETWEEN '" + StartCreatedDate + "' AND '" + EndCreatedDate + "' ");
            if (Author != null && Author.Length > 0) strWhere.Append(" AND Author LIKE '" + Author + "%' ");
            if (DeptName != null && DeptName.Length > 0) strWhere.Append(" AND DeptName LIKE '" + DeptName + "%' ");
            if (Summary != null && Summary.Length > 0) strWhere.Append(" AND Summary LIKE '%" + Summary + "%' ");					
			return (IDataReader)SqlHelper.ExecuteReader(ConnectionString, DatabaseOwner + ObjectQualifier + "gdlmo_Article_GetPagingData", TABLENAME,FIELDLIST,ORDERFIELD,KEYFIELD,PageIndex+1,PageSize,strWhere.ToString(),ORDERTYPE);		
		}
		public override int AddArticleSigned(int ArticleId,int UserId,DateTime SignedDate)
		{
			return int.Parse(SqlHelper.ExecuteScalar(ConnectionString, DatabaseOwner + ObjectQualifier + "gdlmo_ArticleSignedAdd", UserId, ArticleId,  SignedDate).ToString());
		}
		public override IDataReader GetArticleUnSigned(int UserId)
		{
			return (IDataReader)SqlHelper.ExecuteReader(ConnectionString, DatabaseOwner + ObjectQualifier + "gdlmo_ArticleUnSignedGetByUser", UserId); 
		}

		/// <summary>
		/// 根据ArticleId取附件列表
		/// </summary>
		/// <param name="ArticleId">文章id</param>
		/// <returns>附件列表</returns>
		public override IDataReader ListAccesory(int ArticleId)
		{
			return (IDataReader)SqlHelper.ExecuteReader(ConnectionString, DatabaseOwner + ObjectQualifier + "gdlmo_AccesoryListByArticleId", ArticleId);
		}
	
		/// <summary>
		/// 取指定Id的附件
		/// </summary>
		/// <param name="AccesoryId">附件Id</param>
		/// <returns>附件</returns>
		public override IDataReader GetAccesory(int AccesoryId)
		{
			return (IDataReader)SqlHelper.ExecuteReader(ConnectionString, DatabaseOwner + ObjectQualifier + "gdlmo_AccesoryGet", AccesoryId);
		}

		/// <summary>
		/// 获取未签收用户
		/// </summary>
		/// <param name="PortalId">门户编号</param>
		/// <param name="RoleId">角色编号</param>
		/// <param name="ArticleId">文章编号</param>
		/// <returns>未签收用户</returns>
		public override IDataReader GetUnsignedUsers(int PortalId,int RoleId,int ArticleId)
		{
			return (IDataReader)SqlHelper.ExecuteReader(ConnectionString, DatabaseOwner + ObjectQualifier + "gdlmo_ArticleUnSignedUsers", PortalId,RoleId,ArticleId);
		}

		public override IDataReader GetArticleManagerModules(int PortalId)
		{
			return (IDataReader)SqlHelper.ExecuteReader(ConnectionString, DatabaseOwner + ObjectQualifier + "gdlmo_ArticleGetManagerModules",PortalId);
		}
		#endregion

		#region "Category Methods"
		public override IDataReader GetCategory(int CategoryID, int moduleId)
		{
			return (IDataReader)SqlHelper.ExecuteReader(ConnectionString, DatabaseOwner + ObjectQualifier + "gdlmo_CategoryGet", CategoryID,moduleId);
		}
		
		public override IDataReader ListCategory()
		{
			return (IDataReader)SqlHelper.ExecuteReader(ConnectionString, DatabaseOwner + ObjectQualifier + "gdlmo_CategoryList");
		}
		
		public override IDataReader GetCategoryByModules(int ModuleID )
		{
			return (IDataReader)SqlHelper.ExecuteReader(ConnectionString, DatabaseOwner + ObjectQualifier + "gdlmo_CategoryGetByModules", ModuleID);
		}
				
		public override int AddCategory(int ModuleID , string Name , string Description , int ParentID , int Depth , int ViewOrder , string AdminRoles , string EditRoles , string ViewRoles , string CreatedByUser , DateTime CreatedDate , bool IsActive)
		{
			return int.Parse(SqlHelper.ExecuteScalar(ConnectionString, DatabaseOwner + ObjectQualifier + "gdlmo_CategoryAdd", ModuleID, Name, Description, ParentID, Depth, ViewOrder, AdminRoles, EditRoles, ViewRoles, CreatedByUser, CreatedDate, IsActive).ToString());
		}
			
		public override void UpdateCategory(int CategoryID, int ModuleID , string Name , string Description , int ParentID , int Depth , int ViewOrder , string AdminRoles , string EditRoles , string ViewRoles , string CreatedByUser , DateTime CreatedDate , bool IsActive)
		{
			SqlHelper.ExecuteNonQuery(ConnectionString, DatabaseOwner + ObjectQualifier + "gdlmo_CategoryUpdate", CategoryID, ModuleID, Name, Description, ParentID, Depth, ViewOrder, AdminRoles, EditRoles, ViewRoles, CreatedByUser, CreatedDate, IsActive);
		}
		
		public override void DeleteCategory(int CategoryID)
		{
			SqlHelper.ExecuteNonQuery(ConnectionString, DatabaseOwner + ObjectQualifier + "gdlmo_CategoryDelete", CategoryID);
		}
		#endregion
	}
}